// 平台管理-mysql服务处理

// 引入数据库模块
const db = require('../config/mysql.js')
const utils = require("../common/utils")
const bcrypt = require('bcryptjs')

// 添加角色
const addRole = (data) => {
  return new Promise((resolve, reject) => {
    let sql = `insert into role values("${data.id}", "${data.role_name}", "${data.description}", 1, "${data.index_page}")`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("添加角色成功")
      return reject("添加角色失败")
    })
  })
}

// 删除角色
const deleteRole = (id) => {
  return new Promise((resolve, reject) => {
    // let sql = `update role set del_flag = 1 where id = "${id}" and del_flag = 0`
    // let sql = `delete from role where id = "${id}"`
    let sql0 = `select * from role_menu where role_id = "${id}"`
    db.query(sql0, (err, result) => {
      if (err) return reject(err)
      // 有角色但没配权限
      if (result.length === 0) {
        let sql =  `DELETE FROM role WHERE id = "${id}"`
        db.query(sql, (err1, result1) => {
          if (err1) return reject(err1)
          if (result1.affectedRows == 1) return resolve("删除角色成功")
          return reject("删除角色失败")
        })
      } else {
        let sql =  `DELETE FROM role, role_menu USING role INNER JOIN role_menu ON role.id = role_menu.role_id WHERE role.id = "${id}"`
        db.query(sql, (err1, result1) => {
          if (err1) return reject(err1)
          if (result1.affectedRows == 2) return resolve("删除角色成功")
          return reject("删除角色失败")
        })
      }
    })
  })
}

// 修改角色
const updateRole = (data) => {
  return new Promise((resolve, reject) => {
    let sql = `update role set role_name = "${data.role_name}", description = "${data.description}", status = ${data.status}, index_page = "${data.index_page}" where id = "${data.id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("修改角色成功")
      return reject("修改角色失败")
    })
  })
}

// 获取角色列表
const getRoleList = (pageNum, pageSize, role_name) => {
  return new Promise((resolve, reject) => {
    let sql = ''
    let sql1 = ''
    if (role_name.trim() != '') {
      sql = `select * from role where role_name like "%${role_name}%" limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
      sql1 = `select count(*) as total from role where role_name like "%${role_name}%"`
    } else {
      sql = `select * from role limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
      sql1 = `select count(*) as total from role`
    }    
    db.query(sql, (err, result) => {
      if (err) return reject(err)    
      db.query(sql1, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 获取角色菜单
const getRolePermission = (role_id) => {
  return new Promise((resolve, reject) => {
    // 获取所有菜单（未被禁用的）
    let sql = `select * from menu where status = 1`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      result.forEach(item => item.status = 0)
      // 角色拥有的菜单
      let sql1 = `select menu_id from role_menu where role_id = "${role_id}"`
      db.query(sql1, (err1, result1) => {
        if (err1) return reject(err1)
        if (result1.length != 0) {    
          const menu = JSON.parse(result1[0].menu_id)
          result.forEach(item => {
            if (menu.findIndex(el => el == item.id) != -1) {
              item.status = 1
            }
          })
        }         
        const menuList = utils.organizeMenuData(result)
        return resolve(menuList)
      })
    })
  })
}

// 配置角色权限（菜单）
const updateRolePermission = (role_id, menu_ids) => {
  return new Promise((resolve, reject) => {
    // 判断角色是否有菜单分配
    db.query(`select role_id from role_menu where role_id = "${role_id}"`, (err, result) => {
      if (err) return reject(err)
      let sql = '' 
      if (result.length == 0) {
        sql = `insert into role_menu values("${role_id}", "${menu_ids}")`
      } else {
        sql = `update role_menu set menu_id = "${menu_ids}" where role_id = "${role_id}"`
      }     
      db.query(sql, (err1, result1) => {
        if (err1) return reject(err1)
        if (result1.affectedRows == 1) return resolve("配置成功")
        return reject("配置失败")
      })
    })    
  })
}

// 获取所有菜单
const getAllMenu = () => {
  return new Promise((resolve, reject) => {
    let sql = `select * from menu`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      const menuList = utils.organizeMenuData(result)
      return resolve(menuList)
    })
  })
}

// 添加菜单
const addMenu = (data) => {
  return new Promise((resolve, reject) => {
    let { name, path, icon, parent_id, status } = data
    const time = utils.formatDate(new Date().getTime())
    let sql = `insert into menu (name, path, icon, parent_id, status, create_time) values("${name}", "${path}", "${icon}", "${parent_id}", ${status}, "${time}")`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("添加成功")
      return reject("添加失败")
    })
  })
}

// 修改菜单
const updateMenu = (data) => {
  return new Promise((resolve, reject) => {
    let { id, parent_id, name, path, icon, status } = data
    let sql = `update menu set name = "${name}", path = "${path}", icon = "${icon}", parent_id = "${parent_id}", status = ${status} where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("修改成功")
      return reject("修改失败")
    })
  })
}

// 删除菜单
const deleteMenu = (id) => {
  return new Promise((resolve, reject) => {
    // 判断是否有子级菜单,有就不能删除
    let sql = `select count(id) as sonCount from menu where parent_id = ${id}`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result[0].sonCount > 0) return reject("该菜单有子级菜单, 不能删除")
      let sql1 = `delete from menu where id = ${id}`
      db.query(sql1, (err1, result1) => {
        if (err1) return reject(err1)
        if (result1.affectedRows == 1) return resolve("删除成功")
        return reject("删除失败")
      })
    })
  })
}

// 添加用户
const addUser = (data) => {
  return new Promise((resolve, reject) => {
    let uuid = `167${utils.randomNumber()}${utils.randomNumber()}`
    let time = utils.formatDate(new Date().getTime())
    let { email, password, role_id, status } = data
    // 是否已存在
    db.query(`select email from user where email = "${email}"`, (err, result) => {
      if (err) return reject(err)
      if (result.length >= 1) return reject("用户已存在")
      // 加密
      password = bcrypt.hashSync(password, 10)
      let sql = `insert into user (id, email, password, role_id, status, register_time) values(?,?,?,?,?,?)`
      db.query(sql, [uuid, email, password, role_id, status, time], (err1, result1) => {
        if (err1) return reject(err1)
        if (result1.affectedRows == 1) return resolve("添加用户成功")
        return reject("添加用户失败")
      })
    })
  })
}

// 删除用户
const deleteUser = (id) => {
  return new Promise((resolve, reject) => {
    let sql = `delete from user where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("删除成功")
      return reject("删除失败")
    })
  })
}

// 修改用户
const updateUser = (data) => {
  return new Promise((resolve, reject) => {
    let { id, email, role_id, status } = data
    let sql = `update user set email = "${email}", role_id = "${role_id}", status = ${status} where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("修改成功")
      return reject("修改失败")
    })
  })
}

// 重置用户密码(1-6)
const resetPassword = (id) => {
  return new Promise((resolve, reject) => {
    let password = bcrypt.hashSync("123456", 10)
    let sql = `update user set password = "${password}" where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("密码重置成功")
      return reject("密码重置失败")
    })
  })
}

// 获取用户列表
const getUserList = (data) => {
  return new Promise((resolve, reject) => {
    let { email, role_id, status, pageNum, pageSize } = data
    let countSql = `select count(id) as total from user`
    let listSql = `select id, email, role_id, status, register_time from user`
    let conditions1 = []
    let conditions2 = []
    if (email != '' && email != undefined) {
      conditions1.push(`email like "%${email}%"`)
      conditions2.push(`email like "%${email}%"`)
    }
    if (role_id != '' && role_id != undefined) {
      conditions1.push(`role_id = "${role_id}"`)
      conditions2.push(`role_id = "${role_id}"`)
    }
    if (status != '' && status != undefined) {
      conditions1.push(`status = "${status}"`)
      conditions2.push(`status = "${status}"`)
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 添加产品分类
const addCategory = (data) => {
  return new Promise((resolve, reject) => {
    let id = `C${utils.randomNumber()}`
    let name = data.name
    let description = data.description || ''
    db.query(`select name from category where name = "${name.trim()}"`, (err, result) => {
      if (err) return reject(err)
      if (result.length > 0) return reject("分类已存在，请不要重复添加")
      let sql = `insert into category values(?,?,?)`
      db.query(sql, [id, name, description], (err1, result1) => {
        if (err1) return reject(err1)
        if (result1.affectedRows == 1) return resolve("添加分类成功")
        return reject("添加分类失败")
      })
    })
  })
}

// 删除产品分类
const deleteCategory = (id) => {
  return new Promise((resolve, reject) => {
    let sql = `delete from category where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("删除分类成功")
      return reject("删除分类失败")
    })
  })
}

// 修改产品分类
const updateCategory = (data) => {
  return new Promise((resolve, reject) => {
    let id = data.id
    let name = data.name
    let description = data.description || ''
    db.query(`select name from category where name = "${name.trim()}"`, (err, result) => {
      if (err) return reject(err)
      if (result.length > 0) return reject("分类已存在，请不要重复添加")
      let sql = `update category set name = "${name}", description = "${description}" where id = "${id}"`
      db.query(sql, (err1, result1) => {
        if (err1) return reject(err1)
        if (result1.affectedRows == 1) return resolve("修改分类成功")
        return reject("修改分类失败")
      })
    })
  })
}

// 获取产品分类
const getCategory = () => {
  return new Promise((resolve, reject) => {
    let sql = `select * from category`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      return resolve(result)
    })
  })
}

// 添加产品
const addProduct = (data) => {
  return new Promise((resolve, reject) => {
    const arr = ['name', 'description', 'cost_price', 'sale_price', 'stock_num', 'img_url', 'status', 'category_id', 'supplier_id', 'unit']
    for (let item of arr) {
      if (!data[item]) {
        data[item] = ''
      }
    }
    let { name, description, cost_price, sale_price, stock_num, img_url, status, category_id, supplier_id, unit } = data
    // 对金额、数字进行处理一下
    cost_price = Number(cost_price).toFixed(2)
    sale_price = Number(cost_price).toFixed(2)
    stock_num = Number(cost_price).toFixed(1)
    status = status == 1 ? 1 : 0    
    let id = `P${utils.randomNumber()}`
    let time = utils.formatDate(new Date().getTime())
    db.query(`select name from product where name = "${name}"`, (err, result) => {
      if (err) return reject(err)
      if (result.length == 1) return reject("产品已存在，请不要重复添加")     
      let sql = `insert into product values("${id}", "${name}", "${description}", ${cost_price}, ${sale_price}, ${stock_num}, "${img_url}", ${status}, "${category_id}","${supplier_id}", "${unit}", "${time}")`
      db.query(sql, (err1, result1) => {
        if (err1) return reject(err1) 
        if (result1.affectedRows == 1) return resolve("添加产品成功")
        return reject("添加产品失败")
      })
    })
  })
}

// 删除产品
const deleteProduct = (id) => {
  return new Promise((resolve, reject) => {
    let sql = `delete from product where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("删除成功")
      return reject("删除失败")
    })
  })
}

// 批量删除产品
const batchDeleteProduct = (data) => {
  return new Promise((resolve, reject) => {
    let ids = data.ids
    if (ids.length === 0) return reject("请选择要删除的产品")
    // 条件组装
    let sql = `delete from product where`
    ids.forEach(item => {
      sql += ` id = "${item}" or`
    }) 
    // 删除多余的
    let fSql = utils.deleteStrByWay(sql, "or")
    db.query(fSql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows === 0) return reject("删除失败")
      return resolve("删除成功")
    })
  })
}

// 修改产品
const updateProduct = (data) => {
  return new Promise((resolve, reject) => {
    let { id, name, description, cost_price, sale_price, stock_num, img_url, status, category_id, supplier_id, unit } = data
    let sql = `update product set name = ?, description = ?, cost_price = ?, sale_price = ?, stock_num = ?,  img_url = ?, status = ?, category_id = ?, supplier_id = ?, unit = ? where id = ?`
    db.query(sql, [name, description, cost_price, sale_price, stock_num, img_url, status, category_id, supplier_id, unit, id], (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("修改成功")
      return reject("修改失败")
    })
  })
}

// 产品上下架
const upDown = (id, operation) => {
  return new Promise((resolve, reject) => {
    let sql = `update product set status = ${operation} where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("操作成功")
      return reject("操作失败")
    })
  })
}

// 产品供应商
const getProductSupplier = () => {
  return new Promise((resolve, reject) => {
    let time = utils.formatDate(new Date().getTime())
    let sql = `select id, name from supplier where end_time > "${time}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      return resolve(result)
    })
  })
}

// 获取产品列表
const getProductList = (data) => {
  return new Promise((resolve, reject) => {
    let { name, category_id, supplier_id, status, pageSize, pageNum } = data
    let countSql = `select count(id) as total from product`
    let listSql = `select * from product`
    let conditions1 = []
    let conditions2 = []
    if (name != '' && name != undefined) {
      conditions1.push(`name like "%${name}%"`)
      conditions2.push(`name like "%${name}%"`)
    }
    if (category_id != '' && category_id != undefined) {
      conditions1.push(`category_id = "${category_id}"`)
      conditions2.push(`category_id = "${category_id}"`)
    }
    if (status != '' && status != undefined) {
      conditions1.push(`status = "${status}"`)
      conditions2.push(`status = "${status}"`)
    }
    if (supplier_id != '' && supplier_id != undefined) {
      conditions1.push(`supplier_id = "${supplier_id}"`)
      conditions2.push(`supplier_id = "${supplier_id}"`)
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 获取供应商列表
const getSupplierList = (data) => {
  return new Promise((resolve, reject) => {
    let { name, contact_phone, address, isDue, pageSize, pageNum } = data
    let countSql = `select count(id) as total from supplier`
    let listSql = `select * from supplier`
    let conditions1 = []
    let conditions2 = []
    if (name != '' && name != undefined) {
      conditions1.push(`name like "%${name}%"`)
      conditions2.push(`name like "%${name}%"`)
    }
    if (contact_phone != '' && contact_phone != undefined) {
      conditions1.push(`contact_phone like "%${contact_phone}%"`)
      conditions2.push(`contact_phone like "%${contact_phone}%"`)
    }
    if (address != '' && address != undefined) {
      conditions1.push(`address like "%${address}%"`)
      conditions2.push(`address like "%${address}%"`)
    }
    if (isDue != '' && isDue != undefined) {
      let cur_time = utils.formatDate(new Date().getTime())
      let direct = isDue == 1 ? '>' : '<'
      conditions1.push(`end_time ${direct} "${cur_time}"`)
      conditions2.push(`end_time ${direct} "${cur_time}"`)
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 添加供应商
const addSupplier = (data) => {
  return new Promise((resolve, reject) => {
    let { name, contact_person, contact_email, contact_phone, address, start_time, end_time } = data
    let id = `S${utils.randomNumber()}`
    let time = utils.formatDate(new Date().getTime())
    // 供应商是否存在
    db.query(`select name from supplier where name = "${name}"`, (err, result) => {
      if (err) return reject(err)
      if (result.length == 1) return reject("供应商已存在，请不要重复添加")     
      let sql = `insert into supplier values("${id}", "${name}", "${contact_person}", "${contact_email}", "${contact_phone}", "${address}", "${start_time}", "${end_time}", "${time}")`
      db.query(sql, (err1, result1) => {
        if (err1) return reject(err1) 
        if (result1.affectedRows == 1) return resolve("添加供应商成功")
        return reject("添加供应商失败")
      })
    })
    
  })
}

// 修改供应商
const updateSupplier = (data) => {
  return new Promise((resolve, reject) => {
    let { id, name, contact_person, contact_email, contact_phone, address, start_time, end_time } = data
    let sql = `update supplier set name = ?, contact_person = ?, contact_email = ?, contact_phone = ?, address = ?,  start_time = ?, end_time = ? where id = ?`
    db.query(sql, [name, contact_person, contact_email, contact_phone, address, start_time, end_time, id], (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("修改成功")
      return reject("修改失败")
    })
  })
}

// 删除供应商
const deleteSupplier = (id) => {
  return new Promise((resolve, reject) => {
    let sql = `delete from supplier where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("删除成功")
      return reject("删除失败")
    })
  })
}

// 获取供应情况
const getCase = (data) => {
  return new Promise((resolve, reject) => {
    let { supplier_id, name, pageSize, pageNum } = data
    let countSql = `select count(*) as total from product`
    let listSql = `select * from product`
    let conditions1 = []
    let conditions2 = []
    if (supplier_id != '' && supplier_id != undefined) {
      conditions1.push(`supplier_id = "${supplier_id}"`)
      conditions2.push(`supplier_id = "${supplier_id}"`)
    }
    if (name != '' && name != undefined) {
      conditions1.push(`name like "%${name}%"`)
      conditions2.push(`name like "%${name}%"`)
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 添加农场
const addLand = (data) => {
  return new Promise((resolve, reject) => {
    let { name, location, area, type, geojson } = data
    let id = `L${utils.randomNumber()}`
    let time = utils.formatDate(new Date().getTime())
    // 农场是否存在
    db.query(`select name from land where name = "${name}" and type = ${type}`, (err, result) => {
      if (err) return reject(err)
      if (result.length == 1) return reject("农场已存在，请不要重复添加")     
      let sql = `insert into land values("${id}", "${name}", "${location}", "${area}", ${type}, "${geojson}", "${time}")`
      db.query(sql, (err1, result1) => {
        if (err1) return reject(err1) 
        if (result1.affectedRows == 1) return resolve("添加农场成功")
        return reject("添加农场失败")
      })
    })
  })
}

// 获取农场列表
const getLandList = (data) => {
  return new Promise((resolve, reject) => {
    let { name, type, pageSize, pageNum } = data
    let countSql = `select count(id) as total from land`
    let listSql = `select * from land`
    let conditions1 = []
    let conditions2 = []
    if (name != '' && name != undefined) {
      conditions1.push(`name like "%${name}%"`)
      conditions2.push(`name like "%${name}%"`)
    }
    if (type != '' && type != undefined) {
      conditions1.push(`type = "${type}"`)
      conditions2.push(`type = "${type}"`)
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 修改农场
const updateLand = (data) => {
  return new Promise((resolve, reject) => {
    let { id, name, location, area, type, geojson } = data
    let sql = `update land set name = ?, location = ?, area = ?, type = ?, geojson = ? where id = ?`
    db.query(sql, [name, location, area, type, geojson, id], (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("修改成功")
      return reject("修改失败")
    })
  })
}

// 删除农场
const deleteLand = (id) => {
  return new Promise((resolve, reject) => {
    let sql = `delete from land where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("删除成功")
      return reject("删除失败")
    })
  })
}

// 添加种植
const addPlant = (data) => {
  return new Promise((resolve, reject) => {
    let { name, category, land_id, area, lifeCycle } = data
    let id = `T${utils.randomNumber()}`
    let time = utils.formatDate(new Date().getTime())
    let sql = `insert into plant values("${id}", "${name}", "${category}", "${land_id}", "${area}", '${lifeCycle}', "0", "${time}")`
    db.query(sql, (err1, result1) => {
      if (err1) return reject(err1) 
      if (result1.affectedRows == 1) return resolve("添加种植成功")
      return reject("添加种植失败")
    })
  })
}

// 获取种植列表
const getPlantList = (data) => {
  return new Promise((resolve, reject) => {
    let { name, category, land_id, yield, pageSize, pageNum } = data
    let countSql = `select count(id) as total from plant`
    let listSql = `select * from plant`
    let conditions1 = []
    let conditions2 = []
    if (name != '' && name != undefined) {
      conditions1.push(`name like "%${name}%"`)
      conditions2.push(`name like "%${name}%"`)
    }
    if (category != '' && category != undefined) {
      conditions1.push(`category = "${category}"`)
      conditions2.push(`category = "${category}"`)
    }
    if (land_id != '' && land_id != undefined) {
      conditions1.push(`land_id = "${land_id}"`)
      conditions2.push(`land_id = "${land_id}"`)
    }
    if (yield != '' && yield != undefined) {
      if (yield == 0) {
        conditions1.push(`yield = 0`)
        conditions2.push(`yield = 0`)
      } else {
        conditions1.push(`yield != 0`)
        conditions2.push(`yield != 0`)
      }
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 收获种植
const yieldPlant = (data) => {
  return new Promise((resolve, reject) => {
    let { id, yield } = data
    let sql = `update plant set yield = "${yield}" where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("收获成功")
      return reject("收获失败")
    })
  })
}

// 添加养殖
const addAnimal = (data) => {
  return new Promise((resolve, reject) => {
    let { name, land_id, area, num, feed_time } = data
    let id = `A${utils.randomNumber()}`
    let time = utils.formatDate(new Date().getTime())
    let sql = `insert into animal values("${id}", "${name}", "${land_id}", "${area}", '${num}', "${time}", "${feed_time}")`
    db.query(sql, (err1, result1) => {
      if (err1) return reject(err1) 
      if (result1.affectedRows == 1) return resolve("添加养殖成功")
      return reject("添加养殖失败")
    })
  })
}

// 获取养殖
const getAnimalList = (data) => {
  return new Promise((resolve, reject) => {
    let { name, land_id, pageSize, pageNum } = data
    let countSql = `select count(id) as total from animal`
    let listSql = `select * from animal`
    let conditions1 = []
    let conditions2 = []
    if (name != '' && name != undefined) {
      conditions1.push(`name like "%${name}%"`)
      conditions2.push(`name like "%${name}%"`)
    }
    if (land_id != '' && land_id != undefined) {
      conditions1.push(`land_id = "${land_id}"`)
      conditions2.push(`land_id = "${land_id}"`)
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 添加设备
const addDevice = (data) => {
  return new Promise((resolve, reject) => {
    let { name, type, land_id, status, location } = data
    let id = `D${utils.randomNumber()}`
    let time = utils.formatDate(new Date().getTime())
    let sql = `insert into device values("${id}", "${name}", "${type}", "${land_id}", "${status}", '${location}', "${time}")`
    db.query(sql, (err1, result1) => {
      if (err1) return reject(err1) 
      if (result1.affectedRows == 1) return resolve("添加设备成功")
      return reject("添加设备失败")
    })
  })
}

// 获取设备列表
const getDeviceList = (data) => {
  return new Promise((resolve, reject) => {
    let { name, land_id, type, pageSize, pageNum } = data
    let countSql = `select count(id) as total from device`
    let listSql = `select * from device`
    let conditions1 = []
    let conditions2 = []
    if (name != '' && name != undefined) {
      conditions1.push(`name like "%${name}%"`)
      conditions2.push(`name like "%${name}%"`)
    }
    if (land_id != '' && land_id != undefined) {
      conditions1.push(`land_id = "${land_id}"`)
      conditions2.push(`land_id = "${land_id}"`)
    }
    if (type != '' && type != undefined) {
      conditions1.push(`type = "${type}"`)
      conditions2.push(`type = "${type}"`)
    }
    if (conditions1.length > 0) {
      listSql += " where " + conditions1.join(' and ')
      countSql += " where " + conditions2.join(' and ')
    }
    listSql +=  ` limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
    db.query(listSql, (err, result) => {
      if (err) return reject(err)    
      db.query(countSql, (err1, result1) => {
        if (err) return reject(err)
         return resolve({
           records: result,
           total: result1[0].total
         })
      })     
    })
  })
}

// 修改设备
const updateDevice = (data) => {
  return new Promise((resolve, reject) => {
    let { id, name, location, status, type, land_id } = data
    let sql = `update device set name = ?, location = ?, status = ?, type = ?, land_id = ? where id = ?`
    db.query(sql, [name, location, status, type, land_id, id], (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("修改成功")
      return reject("修改失败")
    })
  })
}

// 删除设备
const deleteDevice = (id) => {
  return new Promise((resolve, reject) => {
    let sql = `delete from device where id = "${id}"`
    db.query(sql, (err, result) => {
      if (err) return reject(err)
      if (result.affectedRows == 1) return resolve("删除成功")
      return reject("删除失败")
    })
  })
}

module.exports = {
  getRoleList,
  addRole,
  deleteRole,
  updateRole,
  getRolePermission,
  updateRolePermission,
  getAllMenu,
  addMenu,
  updateMenu,
  deleteMenu,
  addUser,
  deleteUser,
  updateUser,
  resetPassword,
  getUserList,
  addCategory,
  deleteCategory,
  updateCategory,
  getCategory,
  addProduct,
  deleteProduct,
  batchDeleteProduct,
  updateProduct,
  upDown,
  getProductSupplier,
  getProductList,
  getSupplierList,
  addSupplier,
  updateSupplier,
  deleteSupplier,
  getCase,
  addLand,
  getLandList,
  updateLand,
  deleteLand,
  addPlant,
  getPlantList,
  yieldPlant,
  addAnimal,
  getAnimalList,
  addDevice,
  getDeviceList,
  updateDevice,
  deleteDevice
}